package com.utils;

import java.lang.reflect.InvocationTargetException;
import java.lang.reflect.Method;
import java.sql.Date;
import java.sql.PreparedStatement;
import java.sql.ResultSet;
import java.sql.SQLException;
import java.util.ArrayList;
import java.util.List;

import com.test.pojo.TUser;

/**
 * 设计的时候，JavaBean类名对应表名，属性对应字段名，差了下划线，到时候转换一下就行了 因为代码是根据类来判断数据库字段的
 * 
 * @author Administrator
 * 
 */
public class SqlUtils {

	public static void main(String[] args) {
		ColInfo cc = getColArr(TUser.class);
		String s = list2Set(cc.cols, ",");
		System.out.println(s);
	}

	/**
	 * 数据库表信息类
	 * 
	 * @author Administrator
	 * 
	 */
	public static class ColInfo {
		public List<String> cols = new ArrayList<String>();// operate_time
		public List<String> methodGet = new ArrayList<String>();// getUserName
		public List<Class> colstype = new ArrayList<Class>(); // java.sql.Date

		public List<String> methodSet = new ArrayList<String>();// setUserName
		public List<String> paramTypes = new ArrayList<String>();// set的参数类型
		public List<String> cols2 = new ArrayList<String>();// 跟set相对应的列名
	}

	/**
	 * 获取表名
	 * 
	 * @param c
	 * @return
	 */
	public static String getTableName(Class c) {
		String tableName = c.getSimpleName();
//		System.out.println("1--" + tableName);
		tableName = StringUtils.camelToUnderline(tableName).replaceFirst("_",
				"");
//		System.out.println("2--" + tableName);
		return tableName;
	}

	/**
	 * 获取数据库表信息
	 * 
	 * @param c
	 * @return
	 */
	public static ColInfo getColArr(Class c) {
		ColInfo cinfo = new ColInfo();
		Method[] ms = c.getMethods();
		List<String> cols = new ArrayList<String>();
		List<String> methodGet = new ArrayList<String>();
		List<Class> colstype = new ArrayList<Class>();

		List<String> methodSet = new ArrayList<String>();
		List<String> paramTypes = new ArrayList<String>();
		List<String> cols2 = new ArrayList<String>();

		for (int i = 0; i < ms.length; i++) {
			if (!ms[i].getReturnType().toString().contains("Object") ) {// 把接口继承下来的属性去掉
				String mname = ms[i].getName();
				if (mname.startsWith("get") && !mname.equals("getClass")) {
					// 字段
					String mname2 = StringUtils.camelToUnderline(mname);// get_user_name
					mname2 = mname2.replaceFirst("get_", "");
//					System.out.println("a----" + mname2);
					cols.add(mname2);

					// 字段返回类型
					Class c2 = ms[i].getReturnType();
//					System.out.println("b----" + c2);
					colstype.add(c2);

					// 该字段get的方法，到时候可以取值插入
					methodGet.add(ms[i].getName());
//					System.out.println("c----" + ms[i].getName());
//					System.out.println("==================" + i);
				} else if (mname.startsWith("set") && !mname.equals("getClass")) {



					Class[] paramTypes2 = ms[i].getParameterTypes();
					for (Class class1 : paramTypes2) {
						if(!class1.getName().contains("Object")){
							// 字段
							String mname2 = StringUtils.camelToUnderline(mname);// get_user_name
							mname2 = mname2.replaceFirst("set_", "");
//							System.out.println("f----" + mname2);
							cols2.add(mname2);

							methodSet.add(ms[i].getName());
//							System.out.println("d--------------------"
//									+ ms[i].getName());
							
							
						paramTypes.add(class1.getName());
//						System.out.println("e-------------------"+ class1.getName());
						
						
						
						}
					}
				}
			}
		}
		cinfo.cols = cols;
		cinfo.cols2 = cols2;
		cinfo.colstype = colstype;
		cinfo.paramTypes = paramTypes;
		cinfo.methodGet = methodGet;
		cinfo.methodSet = methodSet;
		return cinfo;
	}

	/**
	 * 字段名称转为一条字符串以{@code split}隔开
	 * 
	 * @param list
	 * @param split
	 * @return
	 */
	public static String list2str(List list, String split) {
		StringBuffer sb = new StringBuffer();
		for (int i = 0; i < list.size(); i++) {
			sb.append(list.get(i));
			if (i != list.size() - 1) {
				sb.append(split);
			}
		}
		return sb.toString();
	}

	/**
	 * 字段名称转为一条字符串set colName =?,update有用
	 * 
	 * @param list
	 * @param split
	 * @return
	 */
	public static String list2Set(List list, String split) {
		StringBuffer sb = new StringBuffer();
		for (int i = 1; i < list.size(); i++) {
			sb.append(list.get(i) + "=?");
			if (i != list.size() - 1) {
				sb.append(split);
			}
		}
		return sb.toString();
	}

	/**
	 * 字段名称转为一条字符串以，隔开，组装
	 * 
	 * @param list
	 * @return
	 */
	public static String list2colstr(List list) {
		return list2str(list, ",");
	}

	/**
	 * 插入值全部设置为？
	 * 
	 * @param size
	 *            ：插入值个数
	 * @return
	 */
	public static String list2colqqq(int size) {
		StringBuffer sb = new StringBuffer();
		for (int i = 0; i < size; i++) {
			sb.append("?");
			if (i != size - 1) {
				sb.append(",");
			}
		}
		return sb.toString();
	}

	/**
	 * 设置字段值,对value后面的值字段设置值
	 * 
	 * @param obj
	 *            待插入的对象
	 * @param cinfo
	 *            表信息
	 * @param ps
	 */
	public static void setParaByPerstmt(Object obj, ColInfo cinfo,
			PreparedStatement ps) {
		// List<String> cols = cinfo.cols;
		List<String> methodGet = cinfo.methodGet;// 方法的集合
		List<Class> colstype = cinfo.colstype;

		try {
			for (int i = 0; i < colstype.size(); i++) {
				Class c = colstype.get(i);
				String cn = methodGet.get(i);
				// 获得方法名
				Method m = obj.getClass().getMethod(cn, new Class[] {});// getUserName();
				// 加载，即调用函数
				Object v = m.invoke(obj, new Object[] {});

				if (c.getName().equals(String.class.getName())) {
					if(v==null){
						ps.setString(i + 1, null);
					}else{
						ps.setString(i + 1, (String) v);
					}
				} else if (c.getName().equals(Integer.class.getName())) {
					if(v==null){
						ps.setInt(i + 1, -1);
					}else{
						ps.setInt(i + 1, (Integer) v);
					}
				} else if (c.getName().equals(Long.class.getName())) {
					if (v == null)
						ps.setLong(i + 1, -1L);
					else
						ps.setLong(i + 1, (Long) v);
				} else if (c.getName().equals(Date.class.getName())) {
					if (v == null)
						ps.setDate(i + 1, null);
					else
						ps.setDate(i + 1, (Date) v);
				}
			}
		} catch (NoSuchMethodException e) {
			e.printStackTrace();
		} catch (SecurityException e) {
			e.printStackTrace();
		} catch (IllegalAccessException e) {
			e.printStackTrace();
		} catch (IllegalArgumentException e) {
			e.printStackTrace();
		} catch (InvocationTargetException e) {
			e.printStackTrace();
		} catch (SQLException e) {
			e.printStackTrace();
		}
	}

	/**
	 * 设置字段值,对set后面的字段
	 * 
	 * @param obj
	 *            待插入的对象
	 * @param cinfo
	 *            表信息
	 * @param ps
	 */
	public static void setParaByPerstmt2(Object obj, ColInfo cinfo,
			PreparedStatement ps) {
		List<String> cols = cinfo.cols;
		List<String> methodGet = cinfo.methodGet;// 方法的集合
		List<Class> colstype = cinfo.colstype;

		try {
			for (int i = 1; i < colstype.size(); i++) {
				Class c = colstype.get(i);
				String cn = methodGet.get(i);
				// 获得方法名
				Method m = obj.getClass().getMethod(cn, new Class[] {});// getUserName();
				// 加载，即调用函数
				Object v = m.invoke(obj, new Object[] {});

				if (c.getName().equals(String.class.getName())) {
					ps.setString(i, (String) v);
				} else if (c.getName().equals(Integer.class.getName())) {
					ps.setInt(i, (Integer) v);
				} else if (c.getName().equals(Long.class.getName())) {
					ps.setLong(i, (Long) v);
				} else if (c.getName().equals(Date.class.getName())) {
					ps.setDate(i, (Date) v);
				}
			}
		} catch (NoSuchMethodException e) {
			e.printStackTrace();
		} catch (SecurityException e) {
			e.printStackTrace();
		} catch (IllegalAccessException e) {
			e.printStackTrace();
		} catch (IllegalArgumentException e) {
			e.printStackTrace();
		} catch (InvocationTargetException e) {
			e.printStackTrace();
		} catch (SQLException e) {
			e.printStackTrace();
		}
	}

	/**
	 * select设置字段值
	 * 
	 * @param obj
	 *            待插入的对象
	 * @param cinfo
	 *            表信息
	 * @param ps
	 */
	public static void setParaByRs(Object obj, ColInfo cinfo, ResultSet rs) {
		// List<String> cols = cinfo.cols;
		List<String> methodSet = cinfo.methodSet;// 方法的集合
		List<String> paramTypes = cinfo.paramTypes;

		for (int i = 0; i < paramTypes.size(); i++) {
			String c = paramTypes.get(i);
			String cn = methodSet.get(i);
			try {
				if (c.equals(String.class.getName())) {
					Method m = obj.getClass().getMethod(cn, String.class);// setUserName();
					String colNmae = StringUtils.camelToUnderline(cn)
							.replaceFirst("set_", "");// user_name
					String s = rs.getString(colNmae);// 获取该字段的值
					Object v = m.invoke(obj, s);// 给obj对象赋值

				} else if (c.equals(Integer.class.getName())) {
					Method m = obj.getClass().getMethod(cn, Integer.class);// setUserName();
					String colNmae = StringUtils.camelToUnderline(cn)
							.replaceFirst("set_", "");// user_name
					int s = rs.getInt(colNmae);// 获取该字段的值
					Object v = m.invoke(obj, s);// 给obj对象赋值

				} else if (c.equals(Long.class.getName())) {
					Method m = obj.getClass().getMethod(cn, Long.class);// setUserName();
					String colNmae = StringUtils.camelToUnderline(cn)
							.replaceFirst("set_", "");// user_name
					Long s = rs.getLong(colNmae);// 获取该字段的值
					Object v = m.invoke(obj, s);// 给obj对象赋值

				} else if (c.equals(Date.class.getName())) {
					Method m = obj.getClass().getMethod(cn, Date.class);// setUserName();
					String colNmae = StringUtils.camelToUnderline(cn)
							.replaceFirst("set_", "");// user_name
					Date s = rs.getDate(colNmae);// 获取该字段的值
					Object v = m.invoke(obj, s);// 给obj对象赋值

				}
			} catch (Exception e) {
				e.printStackTrace();
			}

		}
	}

}
